Release 10.1A: OpenEdge Data Management:
SQL Development

How lock levels and lock modes interact

Table 8–2 and Table 8–3 describe how the SQL OpenEdge Engine uses locking to produce a desired transaction behavior. The tables identify the requested lock strength based on the transaction isolation level in effect for a given transaction.

Table 8–2: Insert, update, or delete record operations

Isolation	Info schema lock	Table lock	Record lock
READ UNCOMMITTED	S	NL	NL
READ COMMITTED	S	IX	X
REPEATABLE READ	S	IX	X
SERIALIZABLE	S	SIX	X

Table 8–3: Fetch or select record operations

Isolation	Info schema lock	Table lock	Record lock
READ UNCOMMITTED	S	NL	NL
READ COMMITTED	S	IS	S
REPEATABLE READ	S	IS	S
SERIALIZABLE	S	S	S

There are no table or record locks acquired when the transaction isolation level is READ UNCOMMITTED.

In the READ UNCOMMITTED transaction isolation level you maximize concurrency, but you might also read dirty data.

The primary difference between the READ COMMITTED and REPEATABLE READ transaction isolation levels is that while in REPEATABLE READ, individual record locks are held for the duration of the transaction. For example, if your fetch criteria include all companies in the state of Idaho, each record in the result set will remain locked until all of the records meeting the criteria have been read. In the READ COMMITTED transaction isolation level, the record locks are released once the record has been read.

In the SERIALIZABLE transaction isolation level, a share lock on a table is held for the duration of the transaction, preventing any other transaction from updating the table. Any SQL operation that modifies the information schema is upgraded to SERIALIZABLE, regardless of the user’s current transaction setting.